R Notebook

library(tidyverse)
library(scales)
library(plotly)
library(lubridate)

Beds

beds <- read_csv("raw_data/non_covid_raw_data/beds_by_nhs_board_of_treatment_and_specialty.csv") %>% janitor::clean_names()

Warning: One or more parsing issues, see `problems()` for detailsRows: 30458 Columns: 20── Column specification ──────────────────────────────────────────────────────────────────────────────────────────────────────────────────
Delimiter: ","
chr (10): Quarter, QuarterQF, HB, HBQF, Location, LocationQF, Specialty, SpecialtyQF, SpecialtyName, SpecialtyNameQF
dbl  (5): AllStaffedBeddays, TotalOccupiedBeddays, AverageAvailableStaffedBeds, AverageOccupiedBeds, PercentageOccupancy
lgl  (5): AllStaffedBeddaysQF, TotalOccupiedBeddaysQF, AverageAvailableStaffedBedsQF, AverageOccupiedBedsQF, PercentageOccupancyQF
ℹ Use `spec()` to retrieve the full column specification for this data.
ℹ Specify the column types or set `show_col_types = FALSE` to quiet this message.

beds %>% 
  filter(specialty_name == "All Acute") %>% 
  ggplot(aes(x = quarter, y = percentage_occupancy))+
  geom_line(aes(colour = hb), group = 1)+
  facet_wrap(~ hb)

beds <- beds %>% 
  select(-c(2,4,6,8,10,12,14,16,18,20)) %>% 
  filter(!hb %in% c("SB0801", "S92000003"))

beds <- beds %>% 
  filter(!hb %in% c("SB0801", "S92000003")) %>% 
  filter(hb == location)
  
# beds %>% 
# count(specialty_name)

a_e_beds <- beds %>% 
  filter(specialty_name == "Accident & Emergency")

# a&e percentage occupancy by hb over time
a_e_beds %>% 
  group_by(quarter, hb) %>% 
  summarise(mean_perc_occ = mean(percentage_occupancy, na.rm = TRUE)) %>% 
  ggplot(aes(x = quarter, y = mean_perc_occ))+
  geom_line(aes(group = hb, colour = hb))+
  geom_point()+
  theme(axis.text.x = element_text(angle = 45))

`summarise()` has grouped output by 'quarter'. You can override using the `.groups` argument.

a_e_beds

#all time bed occupancy percentage for health boards
a_e_beds %>% 
  group_by(quarter, hb) %>% 
  summarise(mean_perc_occ = mean(percentage_occupancy, na.rm = TRUE)) %>% 
ungroup() %>% 
  group_by(hb) %>% 
  summarise(avg_per_occ_all_time = mean(mean_perc_occ)) %>% 
  arrange(desc(avg_per_occ_all_time)) %>% 
  ggplot(aes(x = hb, y = avg_per_occ_all_time))+
  geom_col()+
  geom_point()+
  theme(axis.text.x = element_text(angle = 45))

`summarise()` has grouped output by 'quarter'. You can override using the `.groups` argument.

# workout the ten largest 
ten_largest_specialities <- beds %>%
  group_by(specialty_name) %>% 
  summarise(mean_avail_staffed_beds = mean(average_available_staffed_beds)) %>% 
  arrange(desc(mean_avail_staffed_beds)) %>% 
  slice_max(mean_avail_staffed_beds, n=10) %>% 
  select(1) %>% 
  pull()



# bed percentage availablity for top ten largest specialities
beds %>%
  filter(specialty_name %in% ten_largest_specialities) %>% 
  group_by(quarter, specialty_name) %>%
  summarise(mean_perc_occ = mean(percentage_occupancy)) %>% 
  ggplot(aes(x = quarter, y = mean_perc_occ))+
  geom_line(aes(colour = specialty_name, group = specialty_name))+
  geom_point()+
  theme(axis.text.x = element_text(angle = 45, hjust = 1))

`summarise()` has grouped output by 'quarter'. You can override using the `.groups` argument.

  
# bed percentage availablity for all acute
  beds %>%
  filter(specialty_name == "All Acute") %>% 
  group_by(quarter, specialty_name) %>%
  summarise(mean_perc_occ = mean(percentage_occupancy)) %>% 
  ggplot(aes(x = quarter, y = mean_perc_occ))+
  geom_line(aes(colour = specialty_name, group = specialty_name))+
  geom_point()+
  theme(axis.text.x = element_text(angle = 45, hjust = 1))

`summarise()` has grouped output by 'quarter'. You can override using the `.groups` argument.

  
  # bed percentage availability for intensive care
beds %>%
  filter(specialty_name == "Intensive Care Medicine") %>% 
  group_by(quarter, specialty_name) %>%
  summarise(mean_perc_occ = mean(percentage_occupancy)) %>% 
  ggplot(aes(x = quarter, y = mean_perc_occ))+
  geom_line(aes(colour = specialty_name, group = specialty_name))+
  geom_point()+
  theme(axis.text.x = element_text(angle = 45, hjust = 1))

`summarise()` has grouped output by 'quarter'. You can override using the `.groups` argument.

NA
NA
NA

a_e_beds %>% 
  group_by(quarter) %>% 
  summarise(mean_available_beds = mean(average_available_staffed_beds, na.rm = TRUE)) %>% 
  ggplot(aes(x = quarter, y = mean_available_beds))+
  geom_line(group ="1")+
  geom_point()+
  theme(axis.text.x = element_text(angle = 45))

---

age_sex <- read_csv("raw_data/non_covid_raw_data/inpatient_and_daycase_by_nhs_board_of_treatment_age_and_sex.csv") %>% janitor::clean_names()

Rows: 129393 Columns: 18── Column specification ──────────────────────────────────────────────────────────────────────────────────────────────────────────────────
Delimiter: ","
chr (12): Quarter, QuarterQF, HB, HBQF, Location, LocationQF, AdmissionType, AdmissionTypeQF, Sex, Age, AverageLengthOfEpisodeQF, Aver...
dbl  (6): Episodes, LengthOfEpisode, AverageLengthOfEpisode, Stays, LengthOfStay, AverageLengthOfStay
ℹ Use `spec()` to retrieve the full column specification for this data.
ℹ Specify the column types or set `show_col_types = FALSE` to quiet this message.

health_board_names <- read_csv("raw_data/non_covid_raw_data/health_board_names.csv")

Rows: 18 Columns: 5── Column specification ──────────────────────────────────────────────────────────────────────────────────────────────────────────────────
Delimiter: ","
chr (3): HB, HBName, Country
dbl (2): HBDateEnacted, HBDateArchived
ℹ Use `spec()` to retrieve the full column specification for this data.
ℹ Specify the column types or set `show_col_types = FALSE` to quiet this message.

age_sex %>% 
  count(hb)

season_age_sex <- age_sex %>% 
  mutate(date = yq(quarter),
         year = year(date),
         month = month(date, label = TRUE, abbr = FALSE),
         season = case_when(
           str_detect(month, "December") ~ "Winter",
           str_detect(month, "January") ~ "Winter",
           str_detect(month, "February") ~ "Winter",
           str_detect(month, "March") ~ "Spring",
           str_detect(month, "April") ~ "Spring",
           str_detect(month, "May") ~ "Spring",
           str_detect(month, "June") ~ "Summer",
           str_detect(month, "July") ~ "Summer",
           str_detect(month, "August") ~ "Summer",
           str_detect(month, "September") ~ "Autumn",
           str_detect(month, "October") ~ "Autumn",
           str_detect(month, "November") ~ "Autumn"),
         season = factor(season, order = TRUE)) 

library(lubridate)
library(zoo)

# change quarter column into the date at the start of each quarter
 age_sex <-  age_sex %>% 
    mutate(quarter = yq(quarter))

 # shows the total length of stay by age bracket for emergency inpatients
age_sex %>% 
  filter(admission_type == "Emergency Inpatients") %>% 
  group_by(quarter, age) %>% 
  summarise(total_length_of_stay = sum(length_of_stay)) %>% 
  ggplot(aes(x = quarter, y = total_length_of_stay))+
  geom_line(aes(colour = age))+
  theme(axis.text.x = element_text(angle = 45))

`summarise()` has grouped output by 'quarter'. You can override using the `.groups` argument.

age_sex %>% 
count(admission_type)

age_sex

 # shows the total length of stay by age bracket for elective inpatients
age_sex %>% 
  filter(admission_type == "Elective Inpatients") %>% 
  group_by(quarter, age) %>% 
  summarise(total_length_of_stay = sum(length_of_stay)) %>% 
  ggplot(aes(x = quarter, y = total_length_of_stay))+
  geom_line(aes(colour = age))+
  theme(axis.text.x = element_text(angle = 45))

`summarise()` has grouped output by 'quarter'. You can override using the `.groups` argument.

 # shows the mean length of stay by age bracket for emergency inpatients
#can facet by sex also if required
age_sex %>% 
  filter(admission_type == "Emergency Inpatients") %>% 
  group_by(quarter, age) %>% 
  summarise(avg_length_of_stay = mean(average_length_of_stay, na.rm = TRUE)) %>% 
  ggplot(aes(x = quarter, y = avg_length_of_stay))+
  geom_line(aes(colour = age, group = age))+
  theme(axis.text.x = element_text(angle = 45))

`summarise()` has grouped output by 'quarter'. You can override using the `.groups` argument.

  #facet_wrap( ~ sex)

 # shows the mean length of stay by age bracket for elective inpatients
# can facet by sex 
# age_sex %>% 
#   filter(admission_type == "Elective Inpatients") %>% 
#   group_by(quarter, age) %>% 
#   summarise(avg_length_of_stay = mean(average_length_of_stay, na.rm = TRUE)) %>% 
#   ggplot(aes(x = quarter, y = avg_length_of_stay))+
#   geom_col(aes(fill = age), position = "dodge")+
#   theme(axis.text.x = element_text(angle = 45))+
#   scale_x_date(breaks = 4)
#   #facet_wrap(~ sex)

library(sf)

scotland <- st_read("../SG_NHS_HealthBoards_2019_shapefile/SG_NHS_HealthBoards_2019.shp")

Reading layer `SG_NHS_HealthBoards_2019' from data source 
  `C:\Users\neilp\Documents\CODECLAN\phs_scotland_group_project\SG_NHS_HealthBoards_2019_shapefile\SG_NHS_HealthBoards_2019.shp' 
  using driver `ESRI Shapefile'
Simple feature collection with 14 features and 4 fields
Geometry type: MULTIPOLYGON
Dimension:     XY
Bounding box:  xmin: 5512.998 ymin: 530250.8 xmax: 470332 ymax: 1220302
Projected CRS: OSGB 1936 / British National Grid

# view(scotland)
# 
head(scotland, 3)

Simple feature collection with 3 features and 4 fields
Geometry type: MULTIPOLYGON
Dimension:     XY
Bounding box:  xmin: 186130 ymin: 530250.8 xmax: 398017.2 ymax: 672679.8
Projected CRS: OSGB 1936 / British National Grid
     HBCode                HBName Shape_Leng Shape_Area                       geometry
1 S08000015    Ayrshire and Arran   679782.3 3408802229 MULTIPOLYGON (((201916.2 60...
2 S08000016               Borders   525406.7 4742684960 MULTIPOLYGON (((345325.9 57...
3 S08000017 Dumfries and Galloway   830301.2 6676314851 MULTIPOLYGON (((266004.4 54...

# 
plot(scotland[-1])

scotland <-  scotland %>% 
  mutate(centres = st_centroid(st_make_valid(geometry))) %>%
    mutate(lat = st_coordinates(centres)[,1],
           long = st_coordinates(centres)[,2],
           target_2007 = target_2007$ae_target_2007,
           target_2018 = target_2018$ae_target_2018,
           change_ae = target_2007 - target_2018)

ggplot(data = scotland) +
    geom_sf(aes(fill = change_ae)) +
    scale_fill_viridis_c(option = "plasma")+
  labs(title = "percent change in A&E depts meeting the 4 hour target 2007 - 2018")

  

ggplot(data = scotland) +
    geom_sf(aes(fill = target_2018)) +
    scale_fill_viridis_c(option = "plasma")+
  labs(title = "Percent of A&E depts making the 4hr target")

ggplot(data = scotland) +
geom_sf(fill = "green")+
ggrepel::geom_text_repel(aes(x = lat , y = long, label = paste(HBCode, HBName, sep = "\n")), min.segment.length = 0.05,size = 3, color = "black", fontface = "bold") +
  theme_void()

library(sf)

scotland_smaller <- scotland %>% # make a smaller version for performance issues
  st_simplify(TRUE, dTolerance = 2000)
#fixes problems caused by above 
scotland_smaller <- sf::st_cast(scotland_smaller, "MULTIPOLYGON")


# 
#   fig <- ggplotly(
#     ggplot(scotland)+
#   geom_sf(aes(fill = HBName))
# )
#   fig

  
  p <- ggplot(scotland_smaller) + 
  geom_sf(aes(fill = HBName, text = paste("<b>", HBName, "</b>\n", HBCode)))+
    theme_void()

Warning: Ignoring unknown aesthetics: text

  p %>%
  ggplotly(tooltip = "text") %>%
  style(hoverlabel = list(bgcolor = "white"), hoveron = "fill")

NA
NA